package sdfi.lee.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Session;

import sdfi.lee.Entity.Email;
import sdfi.lee.Format.DateFormat;

public class EmailDAO {
		public String send(Email email) throws Exception
		{
			Session session=DAO.openSession();
			Connection con=session.connection();
			con.setAutoCommit(false);
			String sql="insert email(status,replayto,replayname,content,senddate)value('0','"+
			email.getRelayTo()+"','"+email.getReplayName()+"','"+email.getContent()+"','"+DateFormat.getDateFormat(new Date(),"yyyy-MM-dd HH:mm:ss")+"')";
			con.createStatement().executeUpdate(sql);
			con.commit();
			con.close();
			DAO.closeSession();
			return "success";
			
		}
		
		public int getEmailcount() 
		{
			Session session=DAO.openSession();
			Connection con=session.connection();
			try
			{
			con.setAutoCommit(false);
			String sql="select count(*) as c from email where 'status'=0";
			System.out.println(sql);
			ResultSet rs=con.createStatement().executeQuery(sql);
			con.commit();
			
			if(rs.next()) {
				System.out.println(rs.getInt("c"));
				return rs.getInt("c");
			}
			else return 0;
			}
			catch(Exception ee)
			{
				ee.printStackTrace();
				return 0;
			}
		}
		
		public List<Email> getEmailByType(int pg,int size,int type)
		{
			List<Email> list=new ArrayList<Email>();
			Session session=DAO.openSession();
			Connection con=session.connection();
			try
			{
				con.setAutoCommit(false);
				String sql="select *from email where email.status="+type+" order  by senddate desc "+" limit "+(pg-1)*size+","+size;
				if(size==-1)
				{
					sql="select *From email where email.status="+type+" order by senddate desc";
				}
				
				ResultSet rs=con.createStatement().executeQuery(sql);
				while(rs.next())
				{
					Email email=new Email();
					email.setContent(rs.getString("content"));
					email.setRelayTo(rs.getString("replayto"));
					email.setReplayName(rs.getString("replayname"));
					email.setSenddate(rs.getDate("senddate"));
					email.setId(rs.getInt("id"));
					list.add(email);
				}
			}
			catch(Exception ee)
			{
				ee.printStackTrace();
			}
			finally
			{
				DAO.closeSession();
			}
			return list;
		}
		public Email getEmailById(Integer id,boolean isRead)
		{
			Email email=new Email();
			Session session=DAO.openSession();
			Connection con=session.connection();
			try
			{	con.setAutoCommit(false);
				Statement st=con.createStatement();
				String sql="select *from email where id="+id;
				if(isRead)
				{
				String sq="update email set status=1 where id="+id;
				st.executeUpdate(sq);
				}
				ResultSet rs=st.executeQuery(sql);
				
				con.commit();
				if(rs.next())
				{
					email.setContent(rs.getString("content"));
					email.setRelayTo(rs.getString("replayto"));
					email.setReplayName(rs.getString("replayname"));
					email.setSenddate(rs.getDate("senddate"));
					email.setId(rs.getInt("id"));
				}
				
				
			}
			catch(Exception ee)
			{
				ee.printStackTrace();
			}
			finally
			{
				DAO.closeSession();
			}
			return  email;
		}
		public int setReaded(String[] ids,String status)
		{
			Session session=DAO.openSession();
			Connection con=session.connection();
			try
			{con.setAutoCommit(false);
				Statement st=con.createStatement();
				
				for(int i=0;i<ids.length;i++)
				{
					st.addBatch("update email set status="+status+" where id="+ids[i]);
				}
				int c[]=st.executeBatch();
				con.commit();
				return c.length;
				
			}
			catch(Exception ee)
			{
				ee.printStackTrace();
			}
			finally
			{
				DAO.closeSession();
			}
			return 0;
		}
		public int deleteEmail(String[] ids)
		{
			Session session=DAO.openSession();
			Connection con=session.connection();
			try
			{con.setAutoCommit(false);
				Statement st=con.createStatement();
				
				for(int i=0;i<ids.length;i++)
				{
					st.addBatch("delete from email where id="+ids[i]);
				}
				int c[]=st.executeBatch();
				con.commit();
				return c.length;
				
			}
			catch(Exception ee)
			{
				ee.printStackTrace();
			}
			finally
			{
				DAO.closeSession();
			}
			return 0;
		}
		
}
